package cn.dida.dao;

import cn.dida.model.Pageable;
import cn.dida.model.Pbc;
import org.apache.commons.lang3.ArrayUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.HashMap;
import java.util.List;

/**
 * 箱规管理 数据交互类
 * @author weiwei
 */
@Repository
public class PbcDao extends DaoBase {
	/**
	 * 箱规管理列表
	 * @param companyId 公司id
	 * @param pcount 每箱数量
	 * @param state 状态（1：正常、2：已停用）
	 * @param page 分页游标
	 * @return Pageable<Pbc> 箱规分页列表
	 */
    public Pageable<Pbc> query(Integer companyId, Integer pcount, Integer state, int page) {
        Object[] args = {};
        StringBuffer sqlQueryResultCount = new StringBuffer("select count(1) from pbc join staff stf on pbc.create_staff_id = stf.id ");
        StringBuffer sqlQueryResult = new StringBuffer("select pbc.*,stf.id 'createStaffId',stf.account 'createStaffAccount' from pbc join staff stf on pbc.create_staff_id = stf.id");

        StringBuffer sqlWhere = new StringBuffer(" where pbc.company_id = ?");
        args = ArrayUtils.add(args, companyId);
        if (state != null) {
            sqlWhere.append(" and pbc.state = ?");
            args = ArrayUtils.add(args, state);
        }
        if (pcount != null) {
            sqlWhere.append(" and pbc.pcount = ?");
            args = ArrayUtils.add(args, pcount);
        }

        // 查询记录总数
        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhere).toString(), Integer.class, args);
        // 查询记录
        sqlQueryResult.append(sqlWhere).append("  order by pbc.create_time desc limit ?,?");
        args = ArrayUtils.add(args, (page - 1) * getPageSize());
        args = ArrayUtils.add(args, getPageSize());
        List<Pbc> result = jdbcTemplate.query(sqlQueryResult.toString(), args, new BeanPropertyRowMapper<>(Pbc.class));
        return new Pageable<>(page, getPageSize(), resultTotal, result);
    }

    /**
     * 根据公司id和状态获取箱规
     * @param companyId	公司id
     * @param state 状态（1：正常、2：已停用）
     * @return List<Pbc> 箱规列表
     */
    public List<Pbc> query(Integer companyId, Integer state) {
        String sql = "select pbc.*,stf.id 'createStaffId',stf.account 'createStaffAccount' from pbc join staff stf on pbc.create_staff_id = stf.id where pbc.company_id = ? and pbc.state = ?";
        List<Pbc> result = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(Pbc.class), companyId, state);
        return result;
    }

    /**
     * 新增箱规
     * @param pbc 箱规实体信息
     * @return int 受影响的记录行数
     */
    public int insert(Pbc pbc) {
        String sql = "insert into pbc(pcount, company_id, state, create_time, create_staff_id) values(:pcount, :company_id, :state, :create_time, :create_staff_id)";
        HashMap<String, Object> paramMap = new HashMap<>();
        paramMap.put("company_id", pbc.getCompanyId());
        paramMap.put("pcount", pbc.getPcount());
        paramMap.put("state", pbc.getState());
        paramMap.put("create_time", new Date());
        paramMap.put("create_staff_id", pbc.getCreateStaffId());
        return namedParameterJdbcTemplate.update(sql, paramMap);
    }
    /**
     * 更新箱规
     * @param pbc 箱规实体信息
     * @return int 受影响的记录行数
     */
    public int update(Pbc pbc) {
        String sql = "update pbc set pcount=:pcount , state=:state where company_id = :company_id and id = :id";
        HashMap<String, Object> paramMap = new HashMap<>();
        paramMap.put("id", pbc.getId());
        paramMap.put("company_id", pbc.getCompanyId());
        paramMap.put("pcount", pbc.getPcount());
        paramMap.put("state", pbc.getState());
        return namedParameterJdbcTemplate.update(sql, paramMap);
    }

    /**
     * 根据公司id和箱规id获取箱规
     * @param companyId 公司id
     * @param id 箱规id
     * @return Pbc 箱规实体信息
     */
    public Pbc get(Integer companyId, Integer id) {
        String sql = "select * from pbc where company_id = ? and id = ?";
        try {
            Pbc pbc = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Pbc.class), companyId, id);
            return pbc;
        } catch (DataAccessException e) {
            return null;
        }
    }

    /**
     * 根据公司id和每箱数量获取箱规
     * @param companyId 公司id
     * @param pcount 每箱数量
     * @return Pbc 箱规实体信息
     */
    public Pbc getByPcount(Integer companyId, Integer pcount) {
        String sql = "select * from pbc where company_id = ? and pcount = ?";
        try {
            Pbc pbc = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Pbc.class), companyId, pcount);
            return pbc;
        } catch (DataAccessException e) {
            return null;
        }
    }

    /**
     * 删除箱规
     * @param companyId 公司id
     * @param id 箱规id
     * @return int 受影响的记录行数
     */
    public int delete(Integer companyId, Integer id) {
        String sql = "delete from pbc where company_id = ? and id = ?";
        return jdbcTemplate.update(sql, companyId, id);
    }
}
